<?php
// $Id$

include_once('BaseModel.class.php');

class MessagingModel extends BaseModel
{
    function groups($f,$t) {
        return $this->select_all("SELECT id,name FROM groups WHERE active=1 AND LEFT(name,1) BETWEEN '$f' AND '$t' ORDER BY name ASC");
    }

    function contacts($f,$t) {
        return $this->select_all("SELECT CONCAT(lastname, ' ', firstname ) as name, m.id 
                                  FROM members m WHERE active=1 AND LEFT(firstname,1) BETWEEN '$f' AND '$t' ORDER BY name ASC");
    }

    function get_msisdn($ids){
        return $this->select_all("SELECT msisdn,type,id FROM members WHERE active=1 AND id IN ($ids) GROUP BY msisdn");
    }

    function smsc() {
        return $this->select_all("SELECT 'auto' as 'network' UNION SELECT DISTINCT  network FROM prefixes WHERE 1");
    }
    function group_members($gid) {
        return $this->select_all("SELECT m.msisdn,m.type,g.id as gid, m.id as mid FROM groups g
                                  JOIN group_members gm ON g.id = gm.group_id
                                  JOIN members m ON m.id = gm.member_id
                                  WHERE g.id IN ($gid) AND m.active=1 GROUP BY m.msisdn");
    }

    function get_failed($mid) {
		$sql="SELECT o.message_id,smsc,msisdn, m.message,o.member_id,o.group_id FROM sms.outbox o 
			  JOIN messages m ON m.id=o.message_id
			  WHERE message_id='$mid' AND status='pending'";
		return $this->select_all($sql);
    }	
    function update_failed($mid, $status='failure'){
             $this->query("UPDATE outbox set status ='$status' WHERE message_id='$mid' and status='pending'");
    }

    # this just remove the country code
    function num_format($msisdn){
        $pattern = array('/i/i','/l/i','/o/i','/[^\d]/','/^(\+63|63)/');
        $replace = array(1,1,0,'','0');
        $msisdn = preg_replace($pattern, $replace, trim($msisdn));
        #if (preg_match("/^(\+63|63|0)([0-9]{1,12})/" , trim($msisdn), $matches))
        #    return "0".$matches[2];
        #else
        return $msisdn;
    }
    function get_network($msisdn){
        $msisdn = $this->num_format($msisdn);
        $prefix = str_split($msisdn,4);
        $sql = "SELECT network FROM prefixes where access LIKE '%{$prefix[0]}%' LIMIT 1";
        $res = $this->select_one($sql);
        return ($res) ? $res[0] : '';
    }

    function send_sms($msisdn, $body, $smsc='',$username='',$member_id='',$group_id='') {
        $msisdn = $this->num_format($msisdn);
        $smsc = ($smsc) ? $smsc : $this->get_network($msisdn); 
        if ($msisdn && $body) {
            $d['message']     = $body;
            $d['by'] = ($username) ? $username : 'system';
            $id = $this->insert('messages', $d);
//            $this->send($id,$msisdn,$smsc,$body);
            $this->send($id,$msisdn,$smsc,$body,$member_id,$group_id);
        }
    }

    function send_group($msisdn,$mid,$smsc,$body,$member_id=0,$group_id=0){
        $msisdn = $this->num_format($msisdn);
        $smsc = ($smsc) ? $smsc : $this->get_network($msisdn); 
        if ($msisdn && $mid) {
            $this->send($mid,$msisdn,$smsc,$body,$member_id,$group_id);
        }
    }

    function send($id,$msisdn,$smsc,$body,$member_id,$group_id){
        $d = Array();
        $d['message_id'] = $id;
        $d['msisdn']     = $msisdn;
        $d['member_id']  = $member_id;
        $d['group_id']   = $group_id;
        $d['smsc']       = ($smsc) ? $smsc : 'globe';
        $d['created_on'] ="NOW()";
        $d['status'] ='pending';
        $id = $this->insert('outbox', $d);

        $dlr = DLR_URL . '&outbox_id=' . $id;
        $smsc = ""; //DISABLE SMSC USE RAMDOM SENDING;
        $url = SEND_URL . '&to=' . $msisdn . '&text=' . urlencode($body) . '&smsc=' . $smsc . '&dlr-url=' . urlencode($dlr);

        $ch = curl_init ($url);
        ob_start();
        curl_exec($ch);
        $str = ob_get_contents();
        ob_end_clean();
        curl_close ($ch);
        $this->query("update outbox set extra = '$str' where id='$id'");
    }

    function get_scheduled(){
        return $this->select_all("SELECT * FROM scheduler WHERE status='pending' and `send_on` < NOW() ;");
    }

    function get_config(){
        $sql = "SELECT config FROM  `dtr_sending_config` WHERE enabled=1 LIMIT 1";
        $res = $this->select_one($sql);
        return ($res) ? $res[0] : 'D';
    
    }

    function get_data($stud_no,$e_date){
        $sql = "SELECT CONCAT(lastname, ' ', firstname ) as stud_name, msisdn, stud_no, members.id, dtr.datein, dtr.id as dtr_id 
                FROM members LEFT JOIN dtr ON members.id=member_id AND datein='$e_date' 
                where stud_no='$stud_no' AND type='student' AND active=1";
        return $this->select_one($sql);
    }
    function get_template($time, $type='in'){
        $sql = "SELECT dtr_time_settings.*, preset_messages.name as  message FROM  `dtr_time_settings` 
		JOIN preset_messages ON preset_messages.id = dtr_time_settings.presetmsg_id 
                WHERE  '$time' BETWEEN  `time_from` AND  `time_to` AND  dtr_time_settings.`name` LIKE  '%$type' LIMIT 1";
        $res = $this->select_one($sql);
        return ($res); # ? $res : $this->select_one("SELECT * FROM dtr_time_settings WHERE name='generic' LIMIT 1");
    }
    function get_studinfo($id){
        $sql ="SELECT * FROM students WHERE stud_no='$id'";
        $res=$this->select_one($sql);
        return ($res);

    }
    function get_empinfo($id){
        $sql ="SELECT *  FROM `employees` WHERE `employee_no` LIKE '$id'";
        $res=$this->select_one($sql);
        return ($res);

    }
    function get_meminfo($id){
        $sql ="SELECT * FROM members WHERE stud_no='$id'";
        $res=$this->select_one($sql);
        return ($res);

    }
    function get_msg(){
        $sql ="SELECT *  FROM `user_templates` WHERE `type` LIKE 'DEFAULT'";
        $res=$this->select_one($sql);
        return ($res);

    }
    function paid_notifs_msg(){
        $sql = "SELECT template FROM `templates` WHERE `name` = 'fullypaid_notifs' ";
        $res=$this->select_one($sql);
        $body = ($res) ? $res['template'] : "Welcome to Marikina Catholic School, you are officially enrolled (date), this will be the schools official mobile number. God Bless";
        return str_replace("(date)", date("D M j Y"),$body);
    }
}


?>
